#!/usr/bin/env python3
#utf-8
import re
import poplib
import base64
from email.parser import Parser
# 用来解析邮件主题
from email.header import decode_header
# 用来解析邮件来源
from email.utils import parseaddr
import pymysql
from bs4 import BeautifulSoup
import time,datetime
from pathlib import Path
import logging
import requests
import random
from urllib import request
import xlrd

import sys, os
path = os.path.abspath('../..')+'/ark_project'
sys.path.append(path)  # 会追加到列表最尾部
from settings import my_settings

class ark_email_spider():
    def __init__(self,settings,logger):
        mysql_settings = settings.mysql_conf
        MYSQL_HOST = mysql_settings['MYSQL_HOST']
        MYSQL_PORT = mysql_settings['MYSQL_PORT']
        MYSQL_DB = mysql_settings['MYSQL_DB']
        MYSQL_USER = mysql_settings['MYSQL_USER']
        MYSQL_PASSWD = mysql_settings['MYSQL_PASSWD']
        MYSQL_CHARTSET = mysql_settings['MYSQL_CHARTSET']

        self.curr_path=os.getcwd()+'/'+'ark_trade/'
        # print('trade====',self.curr_path)

        self.start_date_str = str(time.strftime('%Y-%m-%d', time.localtime()))
        self.logger=logger
        # self.email_done_date_list=open(self.curr_path+'email_done_date.txt','r',encoding='utf-8').readlines()
        # self.email_done_date_list=[each.strip().replace('\ufeff','') for each in self.email_done_date_list]
        # self.fout=open(self.curr_path+'email_done_date.txt','a',encoding='utf-8')
        if not os.path.exists(self.curr_path+'datas'): os.makedirs(self.curr_path+'datas')
        self.data_path=self.curr_path+'datas/'
        self.email_data_date=''

        self.connect = pymysql.connect(
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            database=MYSQL_DB,
            user=MYSQL_USER,
            password=MYSQL_PASSWD,
            charset=MYSQL_CHARTSET
        )
        self.cursor = self.connect.cursor()
        self.run_flag=True
        self.my_user_agent = my_settings.MyUserAgent().my_user_agent
        self.Referer_list=['https://mail.163.com/','https://www.baidu.com/','https://www.foxmail.com/',
                           'https://qiye.aliyun.com/','https://mail.qq.com/','https://mail.sina.com.cn/',
                           'https://mail.sohu.com/','https://outlook.live.com/','https://www.126.com/']

        # self.headers = {
        #     "User-Agent": self.my_user_agent,
        #     'Connection': 'close',
        #     'Referer':random.choice(self.Referer_list)
        #     #https://mail.163.com/
        # }

    def get_email_content(self):
        useraccount = 'tradego_ark'
        password = 'ISAQTMOLGMMNYRSC'

        # 邮件服务器地址,以下为网易邮箱
        pop3_server = 'pop.163.com'

        # 开始连接到服务器
        server = poplib.POP3(pop3_server)

        # 打开或者关闭调试信息，为打开，会在控制台打印客户端与服务器的交互信息
        server.set_debuglevel(1)

        # 打印POP3服务器的欢迎文字，验证是否正确连接到了邮件服务器
        self.logger.info(server.getwelcome().decode('utf8'))

        # 开始进行身份验证
        server.user(useraccount)
        server.pass_(password)

        # 返回邮件总数目和占用服务器的空间大小（字节数）， 通过stat()方法即可
        email_num, email_size = server.stat()
        self.logger.info("消息的数量: {0}, 消息的总大小: {1}".format(email_num, email_size))

        # 使用list()返回所有邮件的编号，默认为字节类型的串
        rsp, msg_list, rsp_siz = server.list()
        self.logger.info("服务器的响应: {0},\n消息列表： {1},\n返回消息的大小： {2}".format(rsp, msg_list, rsp_siz))

        self.logger.info('邮件总数： {}'.format(len(msg_list)))

        # 下面单纯获取最新的一封邮件
        total_mail_numbers = len(msg_list)
        rsp, msglines, msgsiz = server.retr(total_mail_numbers)
        # print("服务器的响应: {0},\n原始邮件内容： {1},\n该封邮件所占字节大小： {2}".format(rsp, msglines, msgsiz))

        # msg_content = b'\r\n'.join(msglines).decode('gbk')
        msg_content = b'\n'.join(msglines).decode('utf-8')
        msg_content=msg_content.replace('=\n','')
        # print(msg_content)

        msg = Parser().parsestr(text=msg_content)
        # print('解码后的邮件信息:\n{}'.format(msg))

        # 关闭与服务器的连接，释放资源
        server.close()

        return msg

    def parser_subject(self,msg):
        subject = msg['Subject']
        value, charset = decode_header(subject)[0]
        if charset:
            value = value.decode(charset)
        self.logger.info('邮件主题： {0}'.format(value))
        return value

    def parser_address(self,msg):
        hdr, addr = parseaddr(msg['From'])
        # name 发送人邮箱名称， addr 发送人邮箱地址
        name, charset = decode_header(hdr)[0]
        if charset:
            name = name.decode(charset)
        self.logger.info('发送人邮箱名称: {0}，发送人邮箱地址: {1}'.format(name, addr))
        return name,addr

    def download_data_excel(self,url,work_path):
        #有效下载excel文件代码
        # url = 'https://u4959697.ct.sendgrid.net/ls/click?upn=zcd6lV4HLKVOGeJ9ek2kSRXRFFQn1rBhvTyMa9aIC2TSfz2mEl5lAXMHUHfvzFMG9EBHI0L9cI-2FhIAdlGLHKOAbmkWWcZh6bmWmTWy343HcPDQALvVV4bjOeIWm1JXQYuNBg_oN-2BONGyN1BoCeXmvWpYlkxTAeDPnqe6UdQS7FFVVG9ex-2BdwsrQpud8I0NL-2BSPgHWQxTxT08968ATXgi5xxO3DBTAy6WTb-2FQ0ha5GPpcpbW-2FEVtI3CeJM50AWlebfXO8vH2d52iBye-2Fmc6yIzhGDGYkx14GY7zWlgpvVMXr7bzhj5ldk3S8Ucj7-2BQrfmH-2BtEptSosNVs4PacYfHeFtuasW5d4EQRWmi5PFKZJ-2FqOf2TowOtHY-2BhSsYyejH5yWg3zfZoOZr1-2B6wxDGodflR40dDzR7vHe3EPph8PMiEV8bS4U-3D'
        opener=request.build_opener()
        opener.addheaders=[('User-Agent',self.my_user_agent),
                           ('Referer',random.choice(self.Referer_list))]
        request.install_opener(opener)
        request.urlretrieve(url,work_path)

    def read_excel_data(self,filepath):
        workbook = xlrd.open_workbook(filepath)

        sheet = workbook.sheet_by_index(0)
        is_right_data = False
        result_datas = []
        for index in range(1, sheet.nrows):
            row_value = sheet.row_values(index)
            row_value = list(filter(None, row_value))
            if len(row_value) != 8:
                continue
            elif len(row_value) == 8 and row_value == ['FUND', 'Date', 'Direction', 'Ticker', 'CUSIP', 'Name', 'Shares',
                                                       '% of ETF']:
                is_right_data = True
            elif len(row_value) == 8 and is_right_data:
                result_datas.append(tuple(row_value))
        return result_datas

    def parser_content(self,msg):
        result_list=[]
        content = msg.get_payload(decode=True)

        # print('==',content)
        soup = BeautifulSoup(content, 'lxml')
        a_list=soup.select('a')

        parser_flag=False
        for a in a_list:
            a_text=a.get_text()
            is_download_a=re.findall('Download[\n\s]*today\'s[\n\s]*trades[\n\s]*in[\n\s]*Excel[\n\s]*format',a_text)
            #如果存在下载链接
            if is_download_a:
                parser_flag=True
                excel_href=a.get('href')
                work_path = os.path.join(self.data_path, self.start_date_str + '.xls')
                # 下载当天的邮件中的数据excel
                if not os.path.exists(work_path):
                    self.logger.info('====开始下载当天的trade excel数据...')
                    self.download_data_excel(excel_href,work_path)
                    result_list=self.read_excel_data(work_path)
                else:#已经下载有，解析已有的
                    self.logger.info('====当天的trade excel数据已经下载到本地，本次仅需解析数据...')
                    result_list = self.read_excel_data(work_path)
        if not parser_flag:
            self.logger.error('=====%s当天运行的的trade没有解析到下载excel数据链接======',self.start_date_str)
            self.run_flag = False
        return result_list

    def get_all_email_content(self):
        useraccount = 'tradego_ark'
        password = 'ISAQTMOLGMMNYRSC'

        # 邮件服务器地址,以下为网易邮箱
        pop3_server = 'pop.163.com'

        # 开始连接到服务器
        server = poplib.POP3(pop3_server)

        # 打开或者关闭调试信息，为打开，会在控制台打印客户端与服务器的交互信息
        server.set_debuglevel(1)

        # 打印POP3服务器的欢迎文字，验证是否正确连接到了邮件服务器
        self.logger.info(server.getwelcome().decode('utf8'))

        # 开始进行身份验证
        server.user(useraccount)
        server.pass_(password)

        # 返回邮件总数目和占用服务器的空间大小（字节数）， 通过stat()方法即可
        email_num, email_size = server.stat()
        self.logger.info("消息的数量: {0}, 消息的总大小: {1}".format(email_num, email_size))

        # 使用list()返回所有邮件的编号，默认为字节类型的串
        rsp, msg_list, rsp_siz = server.list()
        self.logger.info("服务器的响应: {0},\n消息列表： {1},\n返回消息的大小： {2}".format(rsp, msg_list, rsp_siz))

        self.logger.info('邮件总数： {}'.format(len(msg_list)))

        for total_mail_numbers in range(1,len(msg_list)+1):
            rsp, msglines, msgsiz = server.retr(total_mail_numbers)

            msg_content = b'\n'.join(msglines).decode('utf-8')
            msg_content = msg_content.replace('=\n', '')

            msg = Parser().parsestr(text=msg_content)

            yield msg

        # 关闭与服务器的连接，释放资源
        server.close()

    def insert_mysql(self,datas):
        """
        :param datas: [(),(),()]形式的，一次插入多条数据
        :return:
        """
        if datas:
            for data in datas:
                sql='insert into ark_sql_trade(fund,date,direction,ticker,cusip,company,shares_ark,wgtpct_ark) values(%s,%s,%s,%s,%s,%s,%s,%s)'
                param=data
                try:
                    self.cursor.execute(sql,param)
                    self.connect.commit()
                    self.logger.info('===trade邮件数据插入成功===')
                except Exception as e:
                    # self.run_flag=False
                    self.logger.warning('===trade邮件数据插入失败，%s===',param)
                    self.logger.warning(e)
        else:
            self.run_flag = False
            self.logger.error('==========EXCEL的数据解析有误====')


    def run_update_new_email(self):
        # 解析最新的邮件
        msg = self.get_email_content()
        result_list = self.parser_content(msg)
        self.insert_mysql(result_list)

    def run_add_all_email(self):
        # #解析历史所有邮件
        # sql_select_trade_date="select DISTINCT date from ark_sql_trade ORDER BY date DESC;"
        # self.cursor.execute(sql_select_trade_date)
        # email_done_date_list=self.cursor.fetchall()
        # email_done_date_list = [t[0] for t in email_done_date_list]

        #获取现有的存档数据，如果当天已下载，不用再访问邮件下载
        datas_list = os.listdir(self.curr_path+'datas') #单个文件测试时，替换为'datas'
        datas_list = [re.findall('(\S+)\.\S+', i)[0] for i in datas_list]
        datas_list.sort(reverse=True)
        newest_tradedate_date=datas_list[0] #最新的一个存档数据的日期

        if newest_tradedate_date!=self.start_date_str:
            msg_iter=self.get_all_email_content()
            for msg in msg_iter:
                msg_date=msg['Date']
                msg_date_trans=self.UTC2BJS(msg_date)
                # print('msg_date==',msg_date)
                # print('msg_date_trans==',msg_date_trans)
                subject_value=self.parser_subject(msg)
                if isinstance(subject_value,bytes):
                    subject_value=subject_value.decode('utf-8')
                name,addr=self.parser_address(msg)
                if name=='ARK Trading Desk' and addr=='ark@ark-funds.com' and 'ARK Investment Management LLC' in subject_value:
                    # result_list = self.parser_content(msg)  #先解析数据，获取数据的日期
                    # if self.email_data_date not in email_done_date_list:
                    # if msg_date_trans =='2021-08-21':
                    if msg_date_trans == self.start_date_str:
                        result_list = self.parser_content(msg)  # 先解析数据，获取数据的日期
                        self.insert_mysql(result_list)
                        self.logger.info('----Date:%s 邮件解析完成 数据日期:%s-----',msg_date,self.email_data_date)
                        self.save_txt(result_list)
        else:
            self.logger.info('======当日的trade数据已经下载成功，不需要再下载==========')

    @staticmethod
    def UTC2BJS(UTC):
        UTC_format = "%a, %d %b %Y %H:%M:%S %z (UTC)"
        CST_format="%a, %d %b %Y %H:%M:%S %z (CST)"
        # BJS_format = "%Y-%m-%d %H:%M:%S"
        BJS_format = "%Y-%m-%d"
        if 'UTC' in UTC:
            UTC = datetime.datetime.strptime(UTC, UTC_format)
            # 格林威治时间+8小时变为北京时间
            BJS = UTC + datetime.timedelta(hours=8)
            BJS = BJS.strftime(BJS_format)
        elif 'CST' in UTC:
            UTC = datetime.datetime.strptime(UTC, CST_format)
            BJS=UTC.strftime(BJS_format)
        else:
            BJS=''
        return BJS

    def save_txt(self,result_list):
        #[(),(),()]形式的，一次插入多条数据
        file_name=self.start_date_str+'.txt'
        save_fout=open(self.data_path+file_name,'w',encoding='utf-8')
        for each in result_list:
            each =[str(i) for i in each]
            save_fout.write('   '.join(each)+'\n')
        save_fout.close()

    def __del__(self):
        self.cursor.close()
        self.connect.close()

def log_process(log_path,log_rank):
    logger = logging.getLogger()
    logger.setLevel(log_rank)  # or whatever logging.DEBUG
    handler = logging.FileHandler(log_path, 'w', 'utf-8')  # or whatever
    handler.setFormatter(
        logging.Formatter('%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'))  # or whatever
    logger.addHandler(handler)
    return logger

if __name__ == '__main__':
    setting_hostname='96_mysql'
    settings = my_settings.mysqlSettings(hostname=setting_hostname)
    logger=log_process('temp_log',logging.INFO)
    email_spider=ark_email_spider(settings=settings,logger=logger)
    email_spider.run_add_all_email()

